<?php

/**
 * This is the model class for table "tbl_invoice".
 *
 * The followings are the available columns in table 'tbl_invoice':
 * @property string $invoice
 * @property string $regid
 * @property string $clerk
 * @property string $dispatch
 * @property string $custno
 * @property string $locno
 * @property string $pricecode
 * @property string $department
 * @property string $invoicedate
 * @property string $entdate
 * @property string $period
 * @property string $billloc
 * @property string $shipname
 * @property string $shipaddress1
 * @property string $shipaddress2
 * @property string $shipCSZ
 * @property string $invoicetype
 * @property string $agreementno
 * @property string $salesperson
 * @property string $warehouse
 * @property string $jobnumber
 * @property string $jobclass
 * @property string $ponumber
 * @property string $sort
 * @property integer $hold
 * @property string $taxcode
 * @property string $laborcost
 * @property integer $printed
 * @property string $amountcash
 * @property string $amountcharge
 * @property string $amountcreditcard
 * @property string $amountchng
 * @property string $post
 * @property string $exempt
 * @property string $formname
 * @property string $txnid
 * @property string $invoiceamount
 * @property string $quoteorg
 * @property integer $billcomplete
 * @property string $materialcost
 * @property string $othercost
 * @property string $slterms
 * @property string $duedate
 * @property integer $dispnotes
 * @property integer $dispparts
 * @property string $transid
 * @property string $modified
 * @property string $araccountid
 * @property integer $drawrequestno
 * @property integer $readonly
 * @property integer $quotestatus
 * @property integer $billamount
 * @property string $rejectedreason
 * @property string $ptdateexported
 * @property integer $amtappliedcredits
 * @property integer $wh
 * @property string $salesorderstatus
 * @property string $purchasedby
 * @property string $invoiceorg
 * @property integer $postedQuickBook
 * @property integer $discount_percent
 * @property string $discount_memo
 * @property string $discount_amount
 * @property string $discount_tax
 * @property string $discount_total
 */
class Invoice extends CActiveRecord
{
	const TYPE_INVOICE='Invoice';
	const TYPE_QUOTE='Quote';
	const TYPE_SALES_ORDERS='Sales Orders';
	const TYPE_CREDIT_MEMO='Credit Memo';

	const STATUS_PENDING=0;
	const STATUS_REJECTED=1;
	const STATUS_APPROVED=2;
	public $isCreatedByCron;
	protected $taxModel = null;
	protected $_location;
	/**
	 * Returns the static model of the specified AR class.
	 * @return Invoice the static model class
	 */
	public static function model($className=__CLASS__)
	{
		return parent::model($className);
	}

	/**
	 * @return string the associated database table name
	 */
	public function tableName()
	{
		return 'tbl_invoice';
	}

	/**
	 * @return array validation rules for model attributes.
	 */
	public function rules()
	{
		// NOTE: you should only define rules for those attributes that
		// will receive user inputs.
		return array(
			//array('invoice, regid, clerk, dispatch, custno, locno, pricecode, department, period, billloc, shipname, shipaddress1, shipaddress2, shipCSZ, invoicetype, agreementno, salesperson, warehouse, jobnumber, jobclass, ponumber, sort, hold, taxcode, laborcost, printed, amountcash, amountcharge, amountcreditcard, amountchng, post, exempt, formname, txnid, invoiceamount, quoteorg, billcomplete, materialcost, othercost, slterms, dispnotes, dispparts, transid, araccountid, drawrequestno, readonly, quotestatus', 'required'),
			array('postedQuickBook,hold, printed, billcomplete, dispnotes, dispparts, drawrequestno, readonly, quotestatus,salesorderstatus', 'numerical', 'integerOnly' => true),
			array('invoice, laborcost, amountcash, amountcharge, amountcreditcard, amountchng, exempt, invoiceamount, quoteorg, materialcost, othercost', 'length', 'max' => 10),
			array('regid, department', 'length', 'max' => 3),
			//array('salesorderstatus','length','max'=>20),
			array('clerk, pricecode, salesperson, warehouse', 'length', 'max' => 4),
			array('locno, billloc', 'length', 'max' => 5),
			array('custno', 'length', 'max' => 7),
			array('agreementno', 'length', 'max' => 15),
			array('period, sort', 'length', 'max' => 6),
			array('shipname, shipaddress1, shipaddress2', 'length', 'max' => 55),
			array('shipCSZ', 'length', 'max' => 75),
			array('purchasedby,invoicetype, jobnumber', 'length', 'max' => 50),
			array('jobclass', 'length', 'max' => 3),
			array('ponumber,dispatch', 'length', 'max' => 20),
			array('taxcode', 'length', 'max' => 8),
			array('post,nextax,', 'length', 'max' => 1),
			array('salesordersorg,salesorderstatus', 'length', 'max' => 30),
			array('formname', 'length', 'max' => 30),
			array('slterms', 'length', 'max' => 31),
			array('invoice', 'unique', 'className' => 'Invoice', 'attributeName' => 'invoice'),
			array('transid, araccountid', 'length', 'max' => 36),
			array('quotestatus', 'checkRejectedReason', 'allowEmpty' => true),
			array('cost,markupvalue,wh,billamount,amtappliedcredits,taxrate', 'numerical'),
			array('rejectedreason', 'length', 'max' => 99999),
			array('invoicedate','safe'),
			array('invoiceorg,amountcharge,invoiceamount,discounted,discount_percent,
                    discount_memo,discount_amount,discount_tax,discount_total', 'safe')
		);
	}

	public function checkRejectedReason($attributes, $params)
	{
		if ($this->quotestatus != NULL && $this->quotestatus == self::STATUS_REJECTED && $this->invoicetype == self::TYPE_QUOTE)
		{
			if (trim($this->rejectedreason) == '' || $this->rejectedreason == NULL)
			{
				$this->addError('quotestatus', 'Rejected reason need to be added');
			}
		}
	}

	/**
	 * @return array relational rules.
	 */
	public function relations()
	{
		// NOTE: you may need to adjust the relation name and the related
		// class name for the relations automatically generated below.
		return array(
			'customer' => array(self::BELONGS_TO, 'Customers', 'custno'),
			'ledgers' => array(self::HAS_MANY, 'InvoiceLedger', 'invoice'),
			'invoiceModel' => array(self::BELONGS_TO, 'Invoice', 'invoice'),
			'jobModel' => array(self::BELONGS_TO, 'Jobs', 'jobnumber'),
			'contract' => array(self::BELONGS_TO, 'Contracts', 'agreementno'),
			'salesOrderPaymentList' => array(self::HAS_MANY, 'RecLedApply', 'number'),
			'invoicePaymentList' => array(self::HAS_MANY, 'RecLedApply', 'number'),
			'collections' => array(self::HAS_MANY, 'Collection', 'invoice'),
			'creditMemoList' => array(self::HAS_MANY, 'Invoice', 'invoiceorg', 'condition' => 'invoicetype=:type', 'params' => array('type' => self::TYPE_CREDIT_MEMO)),
			'term'=>array(self::BELONGS_TO,'Terms','slterms'),
			'dispatchModel'=>array(self::BELONGS_TO,'Dispatchdetails','dispatch'),
		);
	}

	public function getLastCollection()
	{
		if (is_array($this->collections) && count($this->collections) > 0)
		{
			return $this->collections[count($this->collections) - 1];
		}
		return null;
	}

	public function getSalesOrders()
	{
		if($this->invoicetype == self::TYPE_INVOICE){
			return $this->find('invoice=:invoice and invoicetype=:type', array(
				'invoice' => $this->salesordersorg,
				'type' => self::TYPE_SALES_ORDERS));
		}elseif($this->invoicetype == self::TYPE_QUOTE){
			return $this->find('quoteorg=:invoice and invoicetype=:type',array(
				'invoice'=>$this->invoice,
				'type'=>self::TYPE_SALES_ORDERS
			));
		}
	}

	public function findSalesOrderByInvoice()
	{
		return $this->find('invoice=:salesordersorg and invoicetype=:type', array(
			'salesordersorg' => $this->salesordersorg,
			'type' => self::TYPE_SALES_ORDERS
		));
	}

	public function findInvoiceModelListFromSalesOrder()
	{
		if ($this->invoicetype == self::TYPE_SALES_ORDERS)
		{
			return $this->findAll('salesordersorg=:number and invoicetype=:typeInvoice', array(
				'number' => $this->invoice,
				'typeInvoice' => self::TYPE_INVOICE
			));
		}
		return array();
	}

	public function getInvoiceModel()
	{
		return $this->find('salesordersorg=:salesorders AND invoicetype=:type', array(
			'salesorders' => $this->invoice,
			'type' => self::TYPE_INVOICE
		));
	}

	public function getUnlockPossibility()
	{
		$invSum = 0;
		foreach ($this->contract->invoiceList as $invoice)
		{
			$invSum+= $invoice->amountcharge;
		}

		foreach ($this->contract->cmList as $invoice)
		{
			$invSum-= $invoice->amountchage;
		}
		return $this->getTotal() == $invSum;
	}

	public function canApplySalesOrder()
	{
		$recledApplyCount = Yii::app()->db->createCommand('select count(*) from tbl_rec_led_apply where number=:number and type=:type')
				->bindValues(array(
					'number' => $this->invoice,
					'type' => RecLed::TYPE_DP
				))->queryScalar();
		if ($recledApplyCount > 0)
		{
			if (count($this->findInvoiceModelListFromSalesOrder()) > 0)
			{
				return true;
			}
		}
		return false;
	}

	public function isEditable()
	{
		if ($this->invoicetype == self::TYPE_QUOTE)
		{
			if ($this->getSalesOrders() != NULL)
			{
				return false;
			}
		}
		else if ($this->invoicetype == self::TYPE_SALES_ORDERS)
		{
			if ($this->getInvoiceModel() != NULL)
			{
				return false;
			}
		}
		else if ($this->invoicetype == self::TYPE_INVOICE)
		{
			//check for payment
			if ($this->getTotalPaid() > 0)
			{
				return false;
			}
		}
		return true;
	}

	/**
	 * @return array customized attribute labels (name=>label)
	 */
	public function attributeLabels()
	{
		return array(
			'invoice' => 'Invoice Number',
			'regid' => 'Regid',
			'clerk' => 'Clerk',
			'dispatch' => 'Dispatch',
			'custno' => 'Custno',
			'locno' => 'Locno',
			'pricecode' => 'Price code',
			'department' => 'Department',
			'invoicedate' => 'Invoice Date',
			'entdate' => 'Entdate',
			'period' => 'Period',
			'billloc' => 'Bill to',
			'shipname' => 'Shipname',
			'shipaddress1' => 'Shipaddress1',
			'shipaddress2' => 'Shipaddress2',
			'shipCSZ' => 'Ship Csz',
			'invoicetype' => 'Invoicetype',
			'agreementno' => 'Contract No',
			'salesperson' => 'Sales person',
			'warehouse' => 'Warehouse',
			'jobnumber' => 'Job No',
			'jobclass' => 'Jobclass',
			'ponumber' => 'PO Number',
			'sort' => 'Sort',
			'hold' => 'Place invoice in hold',
			'taxcode' => 'Tax code',
			'laborcost' => 'Laborcost',
			'printed' => 'Printed',
			'amountcash' => 'Amountcash',
			'amountcharge' => 'Amountcharge',
			'amountcreditcard' => 'Amountcreditcard',
			'amountchng' => 'Amountchng',
			'post' => 'Post',
			'exempt' => 'Exempt',
			'formname' => 'Formname',
			'txnid' => 'Txnid',
			'invoiceamount' => 'Invoiceamount',
			'quoteorg' => 'Quoteorg',
			'billcomplete' => 'Billcomplete',
			'materialcost' => 'Materialcost',
			'othercost' => 'Othercost',
			'slterms' => 'Terms',
			'duedate' => 'Duedate',
			'dispnotes' => 'Dispnotes',
			'dispparts' => 'Dispparts',
			'transid' => 'Transid',
			'modified' => 'Modified',
			'araccountid' => 'Araccountid',
			'drawrequestno' => 'Drawrequestno',
			'readonly' => 'Readonly',
			'quotestatus' => 'Quote Status',
			'rejectedreason' => 'Rejected Reason',
			'purchasedby' => 'Purchased By',
			'postedQuickBook' => 'Posted Quickbook'
		);
	}

	const QUICKBOOK_NO = 0;
	const QUICKBOOK_YES = 1;
	const QUICKBOOK_REPOST = -1;

	public function getPostedQuickBookList()
	{
		return array(
			self::QUICKBOOK_NO => 'No',
			self::QUICKBOOK_YES => 'Yes',
			self::QUICKBOOK_REPOST => 'Must Re-post'
		);
	}

	public function getPostedQuickBookText()
	{
		return isset($this->postedQuickBookList[$this->postedQuickBook]) ? $this->postedQuickBookList[$this->postedQuickBook] : null;
	}

	public function getLocation()
	{
		if($this->_location == null){
			$this->_location = Locations::model()->find('custno=:custno AND locno=:locno', array('custno' => $this->custno, 'locno' => $this->locno));
			if($this->_location == null){
				throw new CException("Location with custno:$this->custno and locno$this->locno can not be found");
			}
		}
		return $this->_location;

	}

	public function getTaxModelByZip($zip)
	{
		$tax = Taxcode::model()->find('zipcode=:zipcode', array(
			'zipcode' => $zip
		));
		if ($tax != null)
		{
			return $tax;
		}
		else
		{
			throw new CException('Can not find tax model for this invoice ' . $this->invoice . '. Please check this data again');
		}
	}

	public function getTaxModel()
	{
		$model = null;
		if($this->taxModel!=null){
			return $this->taxModel;
		}else{
			if ($this->version == 1)
			{
				$model= $this->getTaxModelByZip($this->getLocation()->zip);
			}
			else
			{
				$stateData = explode(' ', trim($this->shipCSZ));
				/*
					  print_r($stateData);
					  echo count($stateData);
					  die();
				   */
				$stateName = $stateData[count($stateData) - 2];
				$stateZip = $stateData[count($stateData) - 1];
				$taxCodeNex = TaxcodeNex::model()->find("state='$stateName'");
				$taxModel = $this->getTaxModelByZip($stateZip);
				if (isset($taxCodeNex) && $taxCodeNex->active)
				{

					$taxModel->salestaxrate = $taxModel->nextaxrate;
				}
				$model = $taxModel;
			}
			if($model!=null){
				$this->taxModel = $model;
				return $this->taxModel;
			}
		}

	}

	public function checkNex()
	{
		if ($this->version == 1)
			return false;
		else
		{
			$stateData = explode(' ', $this->shipCSZ);
			$stateName = $stateData[count($stateData) - 2];
			$stateZip = $stateData[count($stateData) - 1];
			$taxCodeNex = TaxcodeNex::model()->find("state='$stateName'");
			if (isset($taxCodeNex) && $taxCodeNex->active)
			{
				return true;
			}
		}
		return false;
	}

	public function isInvoiceBelongToScheduleContract()
	{
		if ($this->agreementno != NULL)
		{
			$n = Yii::app()->db->createCommand('select count(*) from tbl_contracts where contractid=:contractid and (billtype=:monthly or billtype=:quarterly)')
					->bindValues(array(
							'contractid' => $this->agreementno,
							'monthly' => Contracts::TYPE_MONTHLY,
							'quarterly' => Contracts::TYPE_QUARTERLY
						)
					)->queryScalar();
			if ($n > 0)
				return true;
		}
		return false;
	}

	public function beforeValidate()
	{
		if ($this->isNewRecord)
		{
			$this->version = 2;

			$this->invoice = DateUtil::getDefaultYear() . '-' . Counters::model()->getNextNumber(DateUtil::getDefaultYear().'-invoice');
			$this->invoicedate = DateUtil::getDefaultCurrentDateTime();
			$this->period = DateUtil::getDefaultPeriod();

		}
		return parent::beforeValidate();
	}
	public function beforeSave()
	{
		if (!isset(Yii::app()->commandPath))
		{
			if($this->isNewRecord && Yii::app()->user->isGuest == false){
				$user = Users::model()->findByPk(Yii::app()->user->getId());
				$this->clerk = $user->employeeid;
			}
		}

		//set modify date
		$this->modified = DateUtil::getDefaultCurrentDateTime();

		//calculate the duedate
		$term = $this->term!=null?$this->term:$this->customer->term;
		if ($term != NULL)
		{
			$date = new DateTime($this->invoicedate);
			$plusDays = $term->netdue;
			$date->add(new DateInterval("P{$plusDays}D"));
			$this->duedate = $date->format('Y-m-d');
		}



		$this->amountcharge = $this->getTotal();
		$this->invoiceamount = $this->getSubTotal();


		if ($this->invoicetype == self::TYPE_CREDIT_MEMO)
		{
			$this->amountcharge = - abs($this->amountcharge);
			$this->invoiceamount = - abs($this->invoiceamount);
		}
		if ($this->isNewRecord && $this->jobModel != NULL)
		{
			$this->salesorderstatus = $this->jobModel->jobstatus;
		}
		else
		{
			//$this->updateJobStatusBySalesOrder();
		}

		//if($this->status)

		return parent::beforeSave();
	}

	public function afterSave()
	{
		if ($this->isNewRecord)
		{

			Counters::model()->saveNumber(DateUtil::getDefaultYear().'-invoice');
		}
		//InvoiceEmp::model()->updateAll(array('status' => 0), 'invoice=:invoice AND status!=0', array('invoice' => $this->invoice));
		//InvoiceLedger::model()->updateAll(array('status' => 0), 'invoice=:invoice AND status!=0', array('invoice' => $this->invoice));

		return parent::afterSave();
	}

	public function afterDelete()
	{
		InvoiceEmp::model()->deleteAll('invoice=:invoice', array('invoice' => $this->invoice));
		$ledgerList = InvoiceLedger::model()->findAll('invoice=:invoice', array(
			'invoice' => $this->invoice
		));
		foreach ($ledgerList as $ledger)
		{
			$ledger->delete();
		}
		Rec::model()->deleteAll('invoice=:invoice', array('invoice' => $this->invoice));
		ContractSchedule::model()->updateAll(array('invoice' => ''), 'invoice=:invoice', array('invoice' => $this->invoice));
		if ($this->contract != null)
		{
			$this->contract->deleteInvoiceFromSchedule($this->invoice);
		}
		DelInvoice::copyFromInvoiceAndSave($this);

		return parent::afterDelete();
	}

	/*
	 *  @return list of locations related to current customer
	 *
	 */

	public function getBillToList($custno)
	{
		$billList = array();
		$customer = Customers::model()->findByPk($custno);

		$billList[0] = "$customer->firstname $customer->lastname|$customer->add1|
       |$customer->add2|$customer->city $customer->state $customer->zip ";
		$locationModels = Locations::model()->findAll('custno=:custno', array(
			'custno' => $custno));
		$locationList = array();
		foreach ($locationModels as $model)
		{
			$locationList[$model->id] = "$model->locname|$model->add1|
       |$model->add2|$model->city $model->state $model->zip ";
		}
		return $billList + $locationList;
	}

	/*
	 *  @return list of departaments
	 *
	 */

	public function getDepartments()
	{
		$departments = SalesDepartments::model()->findAll();
		$departmentsList = array();
		foreach ($departments as $item)
		{
			$departmentsList[$item->id] = "$item->id - $item->description ";
		}
		return $departmentsList;
	}

	/*
	 * @return list of employers, related to customer and from sales department
	 *
	 * */

	public function getSalesPerson()
	{
		return Employees::model()->findAll('dept=:dept', array(
			'dept' => 'Sales'));
	}

	public function getTaxCodeList()
	{
		$data = Yii::app()->db->createCommand('select code,taxdesc from tbl_taxcodes')->queryAll();
		$rs = array();
		if (count($data) > 0)
		{
			foreach ($data as $i => $item)
			{
				$rs[$item['code']] = $item['code'] . ' - ' . $item['taxdesc'];
			}
		}
		return $rs;
	}

	public function getHoldList()
	{
		return array(
			0 => 'Not On Hold',
			-1 => 'On Hold'
		);
	}

	public function getHoldText()
	{
		return isset($this->holdList[$this->hold]) ? $this->holdList[$this->hold] : NULL;
	}

	public function findDepartmentByContractType($type)
	{
		$text = ContractType::getText($type);
		$text = strtolower($text);

		$list = SalesDepartments::getListData();
		foreach ($list as $key => $item)
		{
			$item = strtolower($item);

			if (stripos($item, $text) !== false && stripos($item, 'contracts') !== false)
			{
				return $key;
			}
		}
		return false;
	}

	public function getTotalTax()
	{
		//labor tax
		//material
		$laborTax = 0;
		$materialTax = 0;
		$tax = $this->getTaxModel();
		$isNex = $this->checkNex();
		if ($tax != NULL && $this->location->isexempt == Locations::TAX_EXEMPT_NO)
		{
			if ($tax->labor == 1)
			{
				$laborList = Yii::app()->db->createCommand('select * from tbl_invoice_emp where invoice=:invoice')->bindValue('invoice', $this->invoice)->queryAll();
				foreach ($laborList as $item)
				{
					if( ! $isNex){
						$laborTax+=($item['laboramount'] + $item['overhead']) * $tax->salestaxrate / 100;
					}else{
						$laborTax+=($item['laboramount'] + $item['overhead']) * $tax->nextaxrate / 100;
					}

				}
			}

			$materialTax = 0;
			foreach($this->ledgers as $ledger){
				$materialTax+=$ledger->getItemTax($tax);
			}

		}
		return ($laborTax + $materialTax);
	}

	public function getSubTotal()
	{
		$materialList = Yii::app()->db->createCommand('select * from tbl_invoice_ledger where invoice=:invoice')->bindValue('invoice', $this->invoice)->queryAll();
		$subtotal = 0;
		foreach ($materialList as $item)
		{
			$subtotal+=$item['price'] * $item['qty'];
		}
		return $subtotal;
	}

	public function getTotal()
	{
		return $this->getTotalTax() + $this->getSubTotal();
	}

	public function getQuoteStatusList()
	{
		return array(
			self::STATUS_PENDING => 'Pending',
			self::STATUS_REJECTED => 'Rejected',
			self::STATUS_APPROVED => 'Approved'
		);
	}

	public function getQuoteStatusText()
	{
		return isset($this->quoteStatusList[$this->quotestatus]) ? $this->quoteStatusList[$this->quotestatus] : NULL;
	}

	public static function getInvoiceTypeList()
	{
		return array(
			self::TYPE_INVOICE => self::TYPE_INVOICE,
			self::TYPE_QUOTE => self::TYPE_QUOTE,
			self::TYPE_SALES_ORDERS => self::TYPE_SALES_ORDERS,
			self::TYPE_CREDIT_MEMO => self::TYPE_CREDIT_MEMO
		);
	}

	public function getInvoiceTypeValue($type)
	{
		$list = self::getInvoiceTypeList();
		return isset($list[$type]) ? $list[$type] : NULL;
	}

	public function checkPaid()
	{
		return abs($this->getPaymentApplied()) == abs($this->getAmountWithDiscount());
	}

	public function getTotalPaid()
	{
		$paid = Yii::app()->db->createCommand('select paid from tbl_rec where invoice=:invoice')->bindValues(array(
			'invoice' => $this->invoice,
		))->queryScalar();
		if ($paid !== false && $paid != NULL)
		{
			return $paid;
		}
		return 0;
	}

	public function getSalesOrderPaymentApplied()
	{
		$applied = Yii::app()->db->createCommand('select sum(paid) from tbl_rec_led_apply where number=:invoice and type=:type')->bindValues(array(
			'invoice' => $this->invoice,
			'type' => RecLed::TYPE_DP
		))->queryScalar();
		if ($applied !== false && $applied != NULL)
		{
			return $applied;
		}
		return 0;
	}

	public function getPaymentApplied()
	{
		$idkey = Yii::app()->dbqb->createCommand('select TxnID from invoice where RefNumber=:invoice')->bindValues(array(
			'invoice' => $this->invoice,
		))->queryScalar();
		$applied = Yii::app()->dbqb->createCommand('select sum(Amount) from cc_quickbooks.linkedtxndetail
WHERE IDKEY = :idkey')->bindValues(array(
					'idkey' => $idkey,
				))->queryScalar();
		if ($applied !== false && $applied != NULL)
		{
			return $applied;
		}
		return 0;
	}

	public function getBalanceDue()
	{
		if ($this->discounted)
		{
			return $this->amountcharge + $this->getPaymentApplied() - $this->discount_percent * $this->amountcharge / 100;
		} else
			return $this->amountcharge + $this->getPaymentApplied();
	}

	public function getAmountOwned()
	{
		return $this->amountcharge - $this->getTotalPaid();
	}

	public function getPaymentList()
	{
		$list = RecLed::model()->findAll('invoice=:invoice', array(
			'invoice' => $this->invoice
		));
		return $list;
	}

	public function hasInvoice()
	{
		$count = Yii::app()->db->createCommand('select count(*) from tbl_invoice where salesordersorg=:number')->bindValue('number', $this->invoice)->queryScalar();
		if ($count > 0)
		{
			return true;
		}
		return false;
	}

	public function isFullyInvoiced()
	{
		$sum = Yii::app()->db->createCommand('select sum(amountcharge) from tbl_invoice where salesordersorg=:number')->bindValue('number', $this->invoice)->queryScalar();
		if ($sum >= $this->amountcharge)
		{
			return true;
		}
		return false;
	}

	public function updateJobStatusBySalesOrder()
	{
		if ($this->invoicetype == Invoice::TYPE_SALES_ORDERS)
		{
			if ($this->jobnumber != NULL)
			{
				$rs = Yii::app()->db->createCommand()->update(
					'tbl_jobs', array('jobstatus' => $this->salesorderstatus), 'jobid=:jobid', array(
					'jobid' => $this->jobnumber
				));

				if ($rs > 0)
					return true;
			}
		}
		return false;
	}

	/*
	 * Copy all invoice_ledger and invoice_emp from an source invoice to THIS invoice
	 */

	public function copyInvoiceItemData($source)
	{
		//copy item and technician
		$itemList = Yii::app()->db->createCommand('select * from tbl_invoice_ledger where invoice like :invoice')
				->bindValue('invoice', $source)->queryAll();
		foreach ($itemList as $item)
		{
			$item['invoice'] = $this->invoice;
			unset($item['id']);
			Yii::app()->db->createCommand()->insert('tbl_invoice_ledger', $item);

		}

		$techList = Yii::app()->db->createCommand('select * from tbl_invoice_emp where invoice like :invoice')
				->bindValue('invoice', $source)->queryAll();
		foreach ($techList as $item)
		{
			$item['invoice'] = $this->invoice;
			unset($item['id']);
			$invoiceEmp = new InvoiceEmp;
			$invoiceEmp->attributes = $item;
			$invoiceEmp->save();
		}
	}

	/*
	 * Create new Invoice with type = Credit Memo, copy all item and return the credit memo
	 */

	public function createCreditMemo($source)
	{
		$invoice = $this->findByPk($source);
		if ($invoice === NULL || $invoice->invoicetype != Invoice::TYPE_INVOICE)
		{
			return NULL;
		}
		$model = new Invoice();
		$model->attributes = $invoice->attributes;

		$model->invoicedate = DateUtil::getDefaultCurrentDateTime();
		$model->quoteorg = $source;
		$model->invoiceorg = $source;
		$model->invoicetype = Invoice::TYPE_CREDIT_MEMO;
		$model->isNewRecord = true;
		$model->clerk = null;
		if ($model->save())
		{
			$model->copyInvoiceItemData($source);
			$model->invoiceorg = $source;
			$model->postedQuickBook = self::QUICKBOOK_NO;
			$model->save();
		}
		return $model;
	}

	public function createQuoteByJob($jobnumber)
	{
		$job = Jobs::model()->findByPk($jobnumber);
		if ($job == NULL)
		{
			return NULL;
		}
		/* @var $job Jobs */
		$location = $job->location;
		$model = new Invoice();

		$model->invoicetype = Invoice::TYPE_QUOTE;
		$customer = $location->customer;
		$model->jobnumber = $jobnumber;
		$model->custno = $location->custno;
		$model->locno = $location->locno;
		$model->shipaddress1 = $location->add1;
		$model->shipaddress2 = $location->add2;
		$model->shipCSZ = "$location->city $location->state $location->zip";
		$model->shipname = $location->locname;
		$model->period = DateUtil::getDefaultPeriod();
		$model->taxcode = $location->taxcode;
		$model->department = $job->department;
		//some default setting from parent company
		$customer = $model->customer;
		$model->salesperson = $customer->salesperson;
		$model->pricecode = $customer->pricecode;
		$model->slterms = $customer->terms;
		$model->invoicedate = DateUtil::getDefaultCurrentDateTime();
		$model->status = 1;
		$model->agreementno = NULL;
		$model->ponumber = $job->customerpo;
		if ($model->save())
		{
			$job->setJobAndBillStatus(NULL, Jobs::BILL_QUOTE_PENDING);
			$job->save();
			return $model;
		}
		return NULL;
	}

	public function createRec($transid)
	{
		$rec = Rec::model()->find('invoice=:invoice', array(
			'invoice' => $this->invoice
		));
		if ($rec == NULL)
		{
			$rec = new Rec;
		}
		$rec->transid = $transid;
		$rec->custno = $this->custno;
		$rec->invoice = $this->invoice;
		$rec->invamt = $this->amountcharge;
		$rec->paid = 0;
		$rec->invdate = $this->invoicedate;

		$rec->dept = $this->department;
		$rec->locno = $this->locno;
		$rec->type = 'I';
		$rec->acct = '1110';
		$rec->period = $this->period;
		if ($rec->save())
		{
			return $rec;
		}
		else
		{
			echo json_encode($rec->getErrors());
			return null;
		}

		return $rec;
	}

	public function createTransactionRecord()
	{
		$total = $this->amountcharge;
		// $transaction = Yii::app()->db->beginTransaction();
		//b.	Accounts Receivable Balance table rec
		//i.	Save 1 row rec, InvAmt = totals of invoice and paid = 0
		$transid = createRandomUUID();
		$rec = $this->createRec($transid);
		if ($rec != NULL)
		{
			//    echo 'Save rec';
			/*
			  c.	Clear any old payment history and log in xlogrec
			  i.	Clear any rows in recled that reference to new invoice number
			  ii.	Save log in xlogrec
			 */
			Yii::app()->db->createCommand()->delete('tbl_xlogrec', 'invoice=:invoice', array(
				'invoice' => $this->invoice
			));

			$xlogrec = new Xlogrec;

			$xlogrec->transdate = DateUtil::getDefaultCurrentDateTime();
			$xlogrec->invoice = $this->invoice;
			$xlogrec->memo = "CancelInvoicePayments: All RecLed deleted for $this->custno $this->invoice";
			$xlogrec->amount = 0;
			$xlogrec->custno = $this->custno;
			if ($xlogrec->save())
			{
				//    echo 'Saved xlogrec';
				/*
				  d.	Save Journal Entries finledger
				  i.	Parts
				  1.	– part value and qty from inventory
				  2.	+ part value and qty to COGS – Material
				  ii.	Tax
				  1.	– tax on sales tax payable account
				  iii.	Accounts Receivable
				  1.	+ total invoice amount in A/R
				 */
				//save total first
				$transcount = 0;
				$transnum = Yii::app()->db->createCommand('select max(cast(trannumber as unsigned)) from tbl_fin_ledger')->queryScalar();
				if ($transnum !== false)
				{
					$transnum++;
				}
				else
				{
					$transnum = 0;
				}
				//echo createRandomUUID();
				$totalFl = new FinLedger;

				$totalFl->transid = $transid;
				$totalFl->trancount = $transcount++;
				$totalFl->trannumber = $transnum;
				$totalFl->accountid = Defaultaccounts::getAccount('Account Receivable');
				$totalFl->amount = $total;
				$totalFl->source = 400;
				$totalFl->transdesc = "Invoice $this->invoice";
				$totalFl->transdate = DateUtil::getDefaultCurrentDateTime();
				$totalFl->periodid = $this->period;
				$totalFl->active = -1;
				$totalFl->voided = 0;
				$totalFl->costtype = 0;
				$totalFl->docnum = $this->invoice;
				$totalFl->transtype = 400;
				$totalFl->save();

				$ledgers = $this->ledgers;
				foreach ($ledgers as $ledger)
				{
					//echo '--' . $ledger->getItemTotal() . '--' . $ledger->getItemTax() . '-' . $ledger->cost;
					/* @var $ledger InvoiceLedger */
					//save subtotal
					$sale = new FinLedger;

					$sale->transid = $transid;
					$sale->trancount = $transcount++;
					$sale->trannumber = $transnum;
					$sale->accountid = '3000';
					$sale->amount = -1 * $ledger->getItemTotal();
					$sale->source = 400;
					$sale->transdesc = "$ledger->partno $ledger->description";

					$sale->transdate = DateUtil::getDefaultCurrentDateTime();
					$sale->periodid = DateUtil::getDefaultPeriod();
					$sale->active = -1;
					$sale->voided = 0;
					$sale->costtype = 100;
					$sale->docnum = $this->invoice;
					$sale->transtype = 400;
					$sale->save();
					//     echo 'Save sale';
					//save tax
					$tax = new FinLedger;

					$tax->transid = $transid;
					$tax->trancount = $transcount++;
					$tax->trannumber = $transnum;
					$tax->accountid = '2330';
					$tax->amount = -1 * $ledger->getItemTax();
					$tax->source = 400;
					$tax->transdesc = "$ledger->partno $ledger->description";
					$tax->transdate = DateUtil::getDefaultCurrentDateTime();
					$tax->periodid = DateUtil::getDefaultPeriod();
					$tax->active = -1;
					$tax->voided = 0;
					$tax->costtype = 0;
					$tax->accrualtype = 200;
					$tax->docnum = $this->invoice;
					$tax->transtype = 400;
					$tax->save();
					//  echo 'Save tax';
					//COGS
					//check debit account
					$debitacc = '4020';
					if ($ledger->sdebit != NULL)
					{
						$debitacc = $ledger->sdebit;
					}
					$cogs = new FinLedger;

					$cogs->transid = $transid;
					$cogs->trancount = $transcount++;
					$cogs->trannumber = $transnum;
					$cogs->accountid = $debitacc;
					$cogs->amount = -1 * $ledger->cost;
					$cogs->source = 400;
					$cogs->transdesc = "$ledger->partno $ledger->description";
					$cogs->transdate = DateUtil::getDefaultCurrentDateTime();
					$cogs->periodid = DateUtil::getDefaultPeriod();
					$cogs->active = -1;
					$cogs->voided = 0;
					$cogs->costtype = 100;
					$cogs->accrualtype = 0;
					$cogs->docnum = $this->invoice;
					$cogs->transtype = 400;
					$cogs->save();

					//     echo 'Save cogs';
					//inventory
					//credit acc
					$creditacc = '1210';
					if ($ledger->scredit != NULL)
						$creditacc = $ledger->scredit;
					$inventory = new FinLedger;

					$inventory->transid = $transid;
					$inventory->trancount = $transcount++;
					$inventory->trannumber = $transnum;
					$inventory->accountid = $creditacc;
					$inventory->amount = $ledger->cost;
					$inventory->source = 400;
					$inventory->transdesc = "$ledger->partno $ledger->description";
					$inventory->transdate = DateUtil::getDefaultCurrentDateTime();
					$inventory->periodid = DateUtil::getDefaultPeriod();
					$inventory->active = -1;
					$inventory->voided = 0;
					$inventory->costtype = 100;
					$inventory->accrualtype = 0;
					$inventory->docnum = $this->invoice;
					$inventory->transtype = 400;
					$inventory->save();
					//  echo 'Saved inv';
				}
			}
		}
		return true;
	}

	public function convertInvoiceRecToSalesOrderRec($invoiceNumber)
	{

		if ($this->invoicetype == Invoice::TYPE_SALES_ORDERS)
		{
			$entryid = Yii::app()->db->createCommand('select id from tbl_rec where invoice=:invoice')
					->bindValues(array('invoice' => $this->invoice))->queryScalar();
			Yii::app()->db->createCommand()->update('tbl_rec', array(
				'invoice' => $invoiceNumber,
				'custno' => $this->custno,
				'locno' => $this->locno
			), 'invoice=:invoice', array(
				'invoice' => $this->invoice
			));
			Yii::app()->db->createCommand()->update('tbl_rec_led', array(
				'custno' => $this->custno,
				'locno' => $this->locno
			), 'entryid=:entryid', array(
				'entryid' => $entryid
			));
			Yii::app()->db->createCommand()->update('tbl_rec_led_apply', array(
				'number' => $invoiceNumber,
				'custno' => $this->custno,
				'locno' => $this->locno
			), 'number=:number', array(
				'number' => $this->invoice
			));
		}
	}

	public function getTransactionsList()
	{
		return Yii::app()->dbqb->createCommand('select TxnID, TxnType, TxnDate, RefNumber, Amount from cc_quickbooks.linkedtxndetail
WHERE IDKEY = (select TxnID from invoice where RefNumber=:invoice)')->bindValues(array(
					'invoice' => $this->invoice,
				))->queryAll();
	}

	public function getAmountWithDiscount()
	{
		if ($this->discounted)
		{
			return $this->amountcharge - $this->discount_percent * $this->amountcharge / 100;
		} else
			return $this->amountcharge;
	}

	/**
	 * Calculate the tax amount of this invoice, include discount amount if needed
	 * @return integer Tax amount
	 */
	public function getTaxAmount(){
		if($this->discounted == 1){
			return ($this->amountcharge - $this->invoiceamount) - $this->discount_tax;
		}else{
			return ($this->amountcharge - $this->invoiceamount);
		}
	}

	/**
	 * This is a function to replace amountcharge attribute because amountcharge doesn't include discount
	 * The different is it will include the discount from function getTaxAmount()
	 * @return integer
	 */
	public function getAmountChargeIncludeDiscount(){
		if($this->discounted ==1 )
			return $this->invoiceamount + $this->getTaxAmount();
		else{
			return $this->amountcharge;
		}
	}

	public function getExemptAmount(){
		$exemptAmount = 0;
		if($this->location!=null){
			if($this->location->isexempt == -1){
				$exemptAmount = 0;
			}else{
				foreach($this->ledgers as $ledger){
					$amount = $ledger->price * $ledger->qty;
					if ($ledger->taxed==0)
						$exemptAmount += $amount;
				}
			}
		}
		if($this->invoicetype == Invoice::TYPE_CREDIT_MEMO){
			$exemptAmount = -$exemptAmount;
		}
		return $exemptAmount;
	}

	public function markDispatchComplete(){
		if( $this->dispatchModel != null )
		{
			$this->dispatchModel->complete = DateUtil::getDefaultCurrentDateTime();
			$this->dispatchModel->invoice = $this->invoice;
			$this->dispatchModel->save();
		}
	}

}

    
